package com.luoxue.execsql.rest.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.luoxue.execsql.rest.bean.datasource.DynamicThreadLocalHolder;
import com.luoxue.execsql.rest.bean.mapper.SqlMapperBean;
import com.luoxue.execsql.rest.bean.request.IRequestExtension;
import com.luoxue.execsql.rest.entity.CommonValidator;
import com.luoxue.execsql.rest.entity.DynamicSql;
import com.luoxue.execsql.rest.entity.ErrorValidator;
import com.luoxue.execsql.rest.mapper.DynamicSqlMapper;
import com.luoxue.execsql.rest.mapper.SqlSnippetMapper;
import com.luoxue.execsql.common.util.MyException;
import com.luoxue.execsql.common.util.ResultBody;
import org.apache.logging.log4j.util.Strings;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.annotation.Resource;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.TimeUnit;
import java.util.function.Function;
import java.util.function.Supplier;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class ExecSqlService {
    @Resource
    private SqlMapperBean sqlMapperBean;
    @Resource
    private DynamicSqlMapper dynamicSqlMapper;
    @Resource
    private SqlSnippetMapper sqlSnippetMapper;
    @Resource
    private ParameterValidationService parameterValidationService;
    @Resource
    private IRequestExtension requestExtension;

    public Object query(Map<String, Object> param, RequestMethod requestMethod) {
        if (!param.containsKey("sql_tag")) {
            return ResultBody.error("请求参数sql_tag不存在");
        }

        String sqlTag = (String) param.get("sql_tag");
        List<DynamicSql> dynamicSqlList = dynamicSqlMapper.selectBySqlTag(sqlTag);
        if (dynamicSqlList == null || dynamicSqlList.isEmpty()) {
            return ResultBody.error("根据请求参数sql_tag未查询到匹配的动态sql语句");
        }

        if (dynamicSqlList.stream().anyMatch(dynamicSql -> Strings.isBlank(dynamicSql.getSqlResultKey()))) {
            return ResultBody.error("匹配到的动态sql语句中有字段sql_result_key为空");
        }

        List<ErrorValidator> validationMsgList = parameterValidationService.parameterValidate(sqlTag, param);
        // 请求参数验证
        if (validationMsgList != null && !validationMsgList.isEmpty()) {
            return ResultBody.error(ResultBody.ERROR2_CODE, "请求参数验证失败", validationMsgList);
        }
        Supplier<ResultBody> next = () -> {
            this.variableReplace(dynamicSqlList);
            Map<String, Object> resultMap = new ConcurrentHashMap<>();
            try {
                batchExecSql(param, dynamicSqlList, resultMap);
            } catch (MyException ignored) {
            }
            return ResultBody.ok(resultMap);
        };
        //noinspection ReactiveStreamsUnusedPublisher
        return requestExtension.addParam(param, next);
    }

    @Transactional
    public void batchExecSql(Map<String, Object> param, List<DynamicSql> dynamicSqlList, Map<String, Object> resultMap) throws MyException {
        dynamicSqlList.forEach(dynamicSql -> resultMap.put(dynamicSql.getSqlResultKey(), "..."));
        for (DynamicSql dynamicSql : dynamicSqlList) {
            String dataSourceName = dynamicSql.getDataSourceName();
            if (Strings.isNotBlank(dataSourceName)) {
                DynamicThreadLocalHolder.setDataSource(dataSourceName);
            }
            querySql(param, resultMap, dynamicSql);
            DynamicThreadLocalHolder.clearDataSource();
        }
    }


    private void variableReplace(List<DynamicSql> dynamicSqls) {
        String pattern = "\\{\\{\\s*(\\w+)\\s*}}";
        Pattern r = Pattern.compile(pattern);
        Set<String> nameSet = new HashSet<>();

        // sql语句模板变量查找
        dynamicSqls.forEach(dynamicSql -> {
            String sqlContent = dynamicSql.getSqlContent();
            Matcher m = r.matcher(sqlContent);
            while (m.find()) {
                nameSet.add(m.group(1));
            }
            dynamicSql.setSqlContent(sqlContent.replaceAll(pattern, "\\{\\{$1}}"));
        });

        Map<String, String> sqlSnippetMap = new HashMap<>();

        if (!nameSet.isEmpty()) {
            // 数据库中查询sql片段列表
            sqlSnippetMapper.selectByNames(nameSet).forEach(sqlSnippet -> sqlSnippetMap.put(sqlSnippet.getName(), sqlSnippet.getContent()));
        }

        Pattern commentsP = Pattern.compile("(?ms)('(?:''|[^'])*'|\"(?:\"\"|[^\"])*\")|--.*?$|/\\*.*?\\*/");

        // sql语句模板变量替换
        dynamicSqls.forEach(dynamicSql -> {
            String sqlContent = dynamicSql.getSqlContent();
            if (!sqlSnippetMap.isEmpty()) {
                Matcher m = r.matcher(sqlContent);
                StringBuffer sb = new StringBuffer();
                while (m.find()) {
                    String value = sqlSnippetMap.get(m.group(1));
                    if (value == null) {
                        value = m.group(0);
                    }
                    m.appendReplacement(sb, value);
                }
                m.appendTail(sb);
                sqlContent = sb.toString();
            }
            // BEGIN 去除sql注释和多余空格
            sqlContent = commentsP.matcher(sqlContent).replaceAll("$1").replaceAll("[\t\r\n][\t\r\n]?[\t\r\n]?", " ");
            // END 去除sql注释和多余空格

            dynamicSql.setSqlContent(sqlContent);
        });
    }

    private void querySql(Map<String, Object> param, Map<String, Object> resultMap, DynamicSql dynamicSql) throws MyException {
        String sqlContent = dynamicSql.getSqlContent();
        String sqlType = dynamicSql.getSqlType();
        String id = String.valueOf(dynamicSql.getId());

        Object sqlResult;
        try {
            sqlContent = String.format("<script>%s</script>", sqlContent);

            if (Strings.isBlank(sqlContent)) {
                sqlResult = "查询字段sql_content为空";
            } else if (Strings.isBlank(sqlType) || sqlType.equals("list")) {
                sqlResult = sqlMapperBean.selectList(dynamicSql, sqlContent, param);
            } else if (sqlType.equals("page")) {
                int pageNo = 1;
                int pageSize = 30;
                try {
                    if (param.containsKey("page_no")) {
                        pageNo = Integer.parseInt((String) param.get("page_no"));
                    }
                    if (param.containsKey("page_size")) {
                        pageSize = Integer.parseInt((String) param.get("page_size"));
                    }
                } catch (NumberFormatException e) {
                    e.printStackTrace();
                    resultMap.put(dynamicSql.getSqlResultKey(), e.getMessage());
                    return;
                } finally {
                    PageHelper.startPage(pageNo, pageSize);
                    List<Map<String, Object>> list = sqlMapperBean.selectList(dynamicSql, sqlContent, param);
                    //用PageInfo对结果进行包装
                    PageInfo<Map<String, Object>> page = new PageInfo<>(list);
                    Map<Object, Object> pageMap = new HashMap<>();
                    pageMap.put("total", page.getTotal());
                    pageMap.put("rows", page.getList());
                    sqlResult = pageMap;
                }
            } else if (sqlType.equals("object")) {
                sqlResult = sqlMapperBean.selectOne(dynamicSql, sqlContent, param);
            } else if (sqlType.startsWith("@")) {
                sqlType = sqlType.substring(1);
                sqlResult = sqlMapperBean.selectOne(dynamicSql, sqlContent, param).get(sqlType);
            } else if (sqlType.equals("update")) {
                sqlResult = sqlMapperBean.update(dynamicSql, sqlContent, param);
            } else {
                sqlResult = "sql_type无效值";
            }
            resultMap.put(dynamicSql.getSqlResultKey(), sqlResult);
        } catch (Exception e) {
            resultMap.put(dynamicSql.getSqlResultKey(), e.getMessage());
            throw new MyException(e);
        }
    }
}
